﻿SELECT
	[main].object_id AS [ObjectId],
	[main].[name] AS [ObjectName],
	[schema].[name] AS [SchemaName],
	[mod].[definition] AS [Definition],

	-- Parameter information
	[params].[name] AS [ParameterName],
	[params].[is_output] AS [IsOutputParameter],
	[params].[is_cursor_ref] AS [IsCursorReference],
	[params].[is_xml_document] AS [IsCompleteXmlDocument],
	[params].[has_default_value] AS [HasDefaultValue],
	[params].[default_value] AS [DefaultValue],
	[params].[xml_collection_id] AS [XmlCollectionId],
	
	-- Type information
	[systypes].[name] AS [TypeName],
	[params].[max_length] AS [MaxLength],
	[params].[precision] AS [Precision],
	[params].[scale] AS [Scale],
	
	-- sysModule Information
	[mod].[uses_ansi_nulls] AS [UsesAnsiNulls],
	[mod].[uses_quoted_identifier] AS [UsesQuotedIdentifier],
	[mod].[is_schema_bound] AS [IsSchemaBound],
	[mod].[uses_database_collation] AS [UsesDatabaseCollation],
	[mod].[is_recompiled] AS [IsRecompiled],
	[mod].[execute_as_principal_id] AS [ExecuteAsPrincipalId]

FROM sys.objects [main]
INNER JOIN sys.schemas [schema] ON [main].[schema_id] = [schema].[schema_id]
INNER JOIN sys.sql_modules [mod] ON [main].object_id = [mod].object_id
LEFT OUTER JOIN sys.parameters [params] ON [main].object_id = [params].object_id
LEFT OUTER JOIN sys.systypes [systypes] ON [params].[system_type_id] = [systypes].[xtype]
WHERE
	[main].[type] IN ('FN', 'IF', 'TF')
-- [WHERECLAUSE]
ORDER BY
	[main].[Name],
	[params].[parameter_id]
